03 March 2025

Database Changes for 4.33

This section contains details of database changes between the 4.32 and 4.33 releases.

You can also download the 4.33 data dictionary (which contains all database tables and entity relationship diagrams) on the Communities website here.

Table changes:

The following tables have been added.

Table Name Column Name
HOURS_STATISTICS A table to store CDR Hours Statistics Field.

The following columns have been added.

Table Name Column Name Type (Size) Nullable Description
GROUP_DEFS GROUP_APP_ID VARCHAR2(40) Y Optional application category for this group. [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=GROUP_APP_ID)]
LEARNER_AIMS DEVOLVED_AREA_MONITORING_5 VARCHAR2(3) Y Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]
LEARNER_AIMS DEVOLVED_AREA_MONITORING_6 VARCHAR2(3) Y Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]
LEARNER_AIMS OFF_THE_JOB_ACT_TRAINING_HOURS NUMBER(4) Y Collects the total Actual off-the-job training hours.
PEOPLE_ADDITIONAL_DETAILS DECLARED_SEX VARCHAR2(40) Y Declared (not legal) Sex associated with the person. [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=DECLARED_SEX)]
PEOPLE_ADDITIONAL_DETAILS GENDER VARCHAR2(40) Y Gender associated with the person. [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=GENDER)]
PEOPLE_ADDITIONAL_DETAILS PRONOUNS VARCHAR2(40) Y Pronouns associated with the person. [FK=VERIFIERS.LOW_VALUE(RV_DOMAIN=PRONOUNS)]
PEOPLE_UNITS_CDR ES_FUND_FLAG VARCHAR2(1) Y Indicates whether the Essential Skills enrolment should be funded by FLU.
PEOPLE_UNITS_CDR PROG_ACHIEVEMENT VARCHAR2(2) Y The overall achievement for the program [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=PROG_ACHIEVEMENT)].
PEOPLE_UNITS_CDR PROG_FAST_TRACK VARCHAR2(1) Y Indicates whether the enrolment is on fast track for the program [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=PROG_FAST_TRACK)].
PEOPLE_UNITS_CDR RETURN_IND VARCHAR2(1) Y Indicates whether the record is to be included in any data returns [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=RETURN_IND)].
PEOPLE_UNITS_CDR TAKE_EXAM_YN VARCHAR2(1) Y Indicates whether the student intends to take the exam at the end of the enrolment or not.
PEOPLE_UNITS_SPECIAL DEVOLVED_AREA_MONITORING_5 VARCHAR2(3) Y Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]
PEOPLE_UNITS_SPECIAL DEVOLVED_AREA_MONITORING_6 VARCHAR2(3) Y Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]
PEOPLE_UNITS_SPECIAL OFF_THE_JOB_ACT_TRAINING_HOURS NUMBER(4) Y Collects the total Actual off-the-job training hours.
UIO_CDR ES_FUND_FLAG VARCHAR2(1) Y Indicates whether the Essential Skills enrolment should be funded by FLU.
UIO_CDR MAIN_CAMPUS VARCHAR2(10) Y The location code for each campus within the six colleges [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=MAIN_CAMPUS)].
UIO_CDR NOTIONAL_HOURS NUMBER(5,2) Y The required non-classroom hours for the course i.e. student out on placement or tutorials.
UIO_CDR PROG_CODE VARCHAR2(10) Y The code for the program of study, including traineeship [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=PROG_CODE)].
UIO_CDR PROG_YEAR VARCHAR2(1) Y The program year of the enrolment [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=PROG_YEAR)].
UIO_CDR TAKE_EXAM_YN VARCHAR2(1) Y Indicates whether the student intends to take the exam at the end of the enrolment or not.
UIO_CDR TENHOUR_IND VARCHAR2(1) Y Indicates whether the course is longer than 10 hours [FK=CDR_VERIFIERS.CODE (RV_DOMAIN=TENHOUR_IND)].
UIO_QUAL_AIMS DEVOLVED_AREA_MONITORING_5 VARCHAR2(3) Y Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]
UIO_QUAL_AIMS DEVOLVED_AREA_MONITORING_6 VARCHAR2(3) Y Indicates participation in programmes or initiatives in devolved areas. [FK=LSC_VERIFIERS.CODE(RV_DOMAIN=DAM)]

View changes:

The following views have been added:

  • DM_CDR_VALIDATIONOUTPUTSUMMARY
  • EBS_CDRSTATISTICSDATA
  • EBS_ILR_DPOUTCOME_XML_2021
  • EBS_ILR_LEARNERAIMS_XML_2021
  • EBS_ILR_LEARNERHE_XML_2021
  • EBS_ILR_LEARNERS_EMP_XML_2021
  • EBS_ILR_LEARNERS_LLDD_XML_2021
  • EBS_ILR_LEARNERS_WP_XML_2021
  • EBS_ILR_LEARNERS_XML_2021
  • EBS_ILR_LRNRAIM_BLZER_XML_2021
  • EBS_ILR_LRNRAIM_FAM_XML_2021

  • EBS_WC_NOTES_TOPICS

The following columns have been added.

View Name Column Name
DM_ENQ_LEARNER_ENQUIRIES PROGRESS_CODE
DM_ENQ_LEARNER_ENQUIRIES PROGRESS_CODE_DESCRIPTION
DM_ENQ_LEARNER_ENQUIRIES PROGRESS_DATE_D
DM_ENQ_LEARNER_ENQUIRIES PROGRESS_STATUS
DM_ENQ_LEARNER_ENQUIRIES PROGRESS_STATUS_DESCRIPTION
EBS_BULK_ENROLMENT SLDD
EBS_BULK_ENROLMENT TOTAL_S_HOURS
EBS_BULK_ENROLMENT DEVOLVED_AREA_MONITORING_6
EBS_BULK_ENROLMENT RETURN_IND
EBS_BULK_ENROLMENT OFF_THE_JOB_ACT_TRAINING_HOURS
EBS_BULK_ENROLMENT ESER_YN
EBS_BULK_ENROLMENT AWARD_DATE
EBS_BULK_ENROLMENT DEVOLVED_AREA_MONITORING_5
EBS_CDRGENERATERETURN PC3
EBS_CDRGENERATERETURN NIMDM2017_QUINTILES
EBS_CDRGENERATERETURN MDM_RANK2017
EBS_CDRGENERATERETURN MAIN_CAMPUS
EBS_CDRGENERATERETURN MAINSTREAM_YN
EBS_CDRGENERATERETURN DEP_NO_CARE
EBS_CDRGENERATERETURN ALEVEL_GCSE
EBS_CDRGENERATERETURN PRIORITY_AREA
EBS_CDRGENERATERETURN VPRS
EBS_CDRGENERATERETURN TENHOUR_IND
EBS_CDRGENERATERETURN STUDENT_EMAIL
EBS_CDRGENERATERETURN STUDENT_DISAB
EBS_CDRGENERATERETURN PROG_YEAR
EBS_CDRGENERATERETURN PROG_OF_STUDY
EBS_CDRGENERATERETURN PROG_FAST_TRACK
EBS_CDRGENERATERETURN PROG_CODE
EBS_CDRGENERATERETURN PROG_ACHIEVEMENT
EBS_ENROLMENT_PROG_AIMS DEVOLVED_AREA_MONITORING_6
EBS_ENROLMENT_PROG_AIMS OFF_THE_JOB_ACT_TRAINING_HOURS
EBS_ENROLMENT_PROG_AIMS DEVOLVED_AREA_MONITORING_5
EBS_EXAM_CANDIDATES PRONOUNS
EBS_EXAM_CANDIDATES DECLARED_SEX
EBS_EXAM_CANDIDATES GENDER
EBS_ILR_GENERATION_SOURCE DEVOLVED_AREA_MONITORING_5
EBS_ILR_GENERATION_SOURCE OFF_THE_JOB_ACT_TRAINING_HOURS
EBS_ILR_GENERATION_SOURCE DEVOLVED_AREA_MONITORING_6
EBS_LEARNERS DECLARED_SEX
EBS_LEARNERS GENDER
EBS_LEARNERS PRONOUNS
EBS_LEARNERS_DEDUP DECLARED_SEX
EBS_LEARNERS_DEDUP GENDER
EBS_LEARNERS_DEDUP PRONOUNS
EBS_LEARNERS_LLWR PRONOUNS
EBS_LEARNERS_LLWR DECLARED_SEX
EBS_LEARNERS_LLWR GENDER
EBS_LEARNER_AIMS AREA
EBS_LEARNER_AIMS OFF_THE_JOB_ACT_TRAINING_HOURS
EBS_LEARNER_AIMS DEVOLVED_AREA_MONITORING_6
EBS_LEARNER_AIMS DEVOLVED_AREA_MONITORING_5
EBS_LEARNER_ENROLMENTS RETURN_IND
EBS_LEARNER_ENROLMENTS START_TIME
EBS_LEARNER_ENROLMENTS STUDENT_HOURS
EBS_LEARNER_ENROLMENTS TAKE_EXAM_YN
EBS_LEARNER_ENROLMENTS PROG_FAST_TRACK
EBS_LEARNER_ENROLMENTS PROG_ACHIEVEMENT
EBS_LEARNER_ENROLMENTS NOTIONAL_STUDENT_HOURS
EBS_LEARNER_ENROLMENTS LONG_DESCRIPTION
EBS_LEARNER_ENROLMENTS TOTAL_STUDENT_HOURS
EBS_LEARNER_ENROLMENTS END_TIME
EBS_LEARNER_ENROLMENTS COURSE_OCCURRENCE_CODE
EBS_LEARNER_ENROLMENTS ES_FUND_FLAG
EBS_SCHEMA_ENTITIES ROLE
EBS_SCHEMA_ENTITIES ISACTIVE
EBS_SCHEMA_ENTITIES ROLE_DEFS_ID
EBS_SDR_STUDENTS_BASE_2020 RECORD_TYPE
EBS_UIO COURSE_HOURS
EBS_UIO ES_FUND_FLAG
EBS_UIO MAIN_CAMPUS
EBS_UIO NOTIONAL_HOURS
EBS_UIO PROG_CODE
EBS_UIO TENHOUR_IND
EBS_UIO PROG_YEAR
EBS_UIO TAKE_EXAM_YN
EBS_UIO_LEARNING_AIMS DEVOLVED_AREA_MONITORING_5
EBS_UIO_LEARNING_AIMS DEVOLVED_AREA_MONITORING_6
EBS_UIO_LLWR COURSE_HOURS
EBS_UIO_LLWR TENHOUR_IND
EBS_UIO_LLWR TAKE_EXAM_YN
EBS_UIO_LLWR ES_FUND_FLAG
EBS_UIO_LLWR MAIN_CAMPUS
EBS_UIO_LLWR NOTIONAL_HOURS
EBS_UIO_LLWR PROG_CODE
EBS_UIO_LLWR PROG_YEAR
EBS_UNIT_SPECIAL DEVOLVED_AREA_MONITORING_5
EBS_UNIT_SPECIAL DEVOLVED_AREA_MONITORING_6
EBS_UNIT_SPECIAL OFF_THE_JOB_ACT_TRAINING_HOURS

The following columns have been dropped.

View Name Column Name
EBS_CDRGENERATERETURN DISABILITY

Service Pack 1

The following column has been added in 4.33 Service Pack 1.

Table Name Column Name Type (Size) Nullable Description
PEOPLE_HESA NATION Varchar2(2) Y Specifically used for the HESA SA return. This field defines the country of a learner’s legal nationality. LOV selection is drawn from the HESA_Verifiers table where RV_Domain = ‘NATION’.